##!/usr/bin/python
# -*- coding: UTF-8 -*-
import sys
reload(sys)
sys.setdefaultencoding( "utf-8" )

from django.shortcuts import render_to_response
import MySQLdb
import redis
import json

r = redis.Redis(host='localhost',port=6379,db=0)

tongbu_order_cms = """
SELECT product_id,count(*) as c,product_name
FROM t_order 
WHERE product_id IN(31409,31410,31412,31413,31181,31182,31184,31185,31176,31177,31179,31180,
                      31080,31081,31082,31174,30342,30343,30345,30000,30001,30192,29954,29955,
                      29956,30191,29300,29341,28705,28596,27969,27833,27834,27835,27836,27837,
                      27838,27839,27840,27841,27842,27843,27844,27845,27676,27478,27301,31855,31856,31857,31858) AND
      pay_time>=CURDATE()
GROUP BY product_name
ORDER BY c
DESC;
"""

tongbu_ticket_cms="""
SELECT a.CommodityId,count(*)  FROM t_movieticket a, t_member_movieticket_log b
WHERE b.CreateTime>=CURDATE() AND 
      a.MovieTicketId=b.MovieTicketId AND 
      a.TicketSource=2
GROUP BY a.CommodityId;
"""

shoufa_order_cms="""
SELECT COUNT(DISTINCT buyer_id) as c,product_name FROM `t_order`
WHERE product_type IN(21,22) AND 
      price >=1 AND 
      `status` IN(2,4,5,6,9)  AND
      pay_time >= CURDATE()
GROUP BY product_name
ORDER BY c DESC
LIMIT 15;	
"""

shoufa_total_cms="""
SELECT COUNT(DISTINCT buyer_id) FROM `t_order`
WHERE product_type IN(21,22) AND 
      price >=1 AND 
      `status` IN(2,4,5,6,9)  AND
      pay_time >= CURDATE()
"""

cms_conn = MySQLdb.connect(
    host = '172.16.221.62',
    port = 3306,
    user = 'wanglei',
    passwd = 'wl@123',
    db = 'golivecms20',
    charset = 'utf8',
    )
cms_cur = cms_conn.cursor()

def _get_tongbu():
    tongbu_order_count = cms_cur.execute(tongbu_order_cms)
    orders = cms_cur.fetchall()
    tongbu_ticket_count = cms_cur.execute(tongbu_ticket_cms)
    tickets = cms_cur.fetchall()
    ticket_dict = {} 
    tongbu_list = []
    ticket_all = 0
    order_all = 0 
    pm = ""
    for ticket in tickets:
        if int(ticket[0]) in [31409,31410,31412,31413]:
            pm = u"风口青春|Youth in the Air Outlet"
        if int(ticket[0]) in [31181,31182,31184,31185]:
            pm = u"恶灵之门|Apparition"
        if int(ticket[0]) in [31176,31177,31179,31180]:
            pm = u"致我们终将到来的爱情|For Love"
        if int(ticket[0]) in [31855,31856,31857,31858]:
            pm = u"致命怪谈|The Game"
        if int(ticket[0]) in [31080,31081,31082,31174]:
            pm = u"请把你的窗户打开|Please Open Your Window"
        if ticket_dict.get(pm,0)==0:
            ticket_dict[pm] = 1
        else:
            ticket_dict[pm] = ticket_dict[pm] + 1
   
    for order in orders:
        tongbu_dict = {}
        tongbu_dict["name"] = order[2]
        tongbu_dict["order_count"] = int(order[1])
        if order[2] in ticket_dict.keys():
            tongbu_dict["ticket_count"] = int(ticket_dict[order[2]])
        else:
            tongbu_dict["ticket_count"] = 0           
        tongbu_list.append(tongbu_dict)
        order_all = order_all + tongbu_dict["order_count"]
        ticket_all = ticket_all + tongbu_dict["ticket_count"] 

    tongbu_list.append({"name":u"总计","order_count":order_all,"ticket_count":ticket_all})
    return tongbu_list

def _get_shoufa():
    shoufa_list = []
    shoufa_total_count = cms_cur.execute(shoufa_total_cms)
    total = cms_cur.fetchone()[0]
    shoufa_order_count = cms_cur.execute(shoufa_order_cms)
    orders = cms_cur.fetchall()
    for order in orders:
        shoufa_dict = {}   
        shoufa_dict["name"] = order[1]
        shoufa_dict["uv"] = int(order[0])
        shoufa_dict["playrate"] = round(order[0]/float(total)*100,2)
        shoufa_list.append(shoufa_dict)
    return shoufa_list

def slides(req):

    #print _get_shoufa()
    #print _get_tongbu()  
    data = {}  
    #data['boxoffice'],data['cash'],data['increase'] = _get_kpi()
    data['shoufa'] = _get_shoufa()
    data['tongbu'] = _get_tongbu()
    data['kpi_date'] = json.loads(r.get("kpi_date")) 
    data['kpi_boxoffice'] = json.loads(r.get("kpi_boxoffice"))
    data['kpi_cash'] = json.loads(r.get("kpi_cash"))
    data['kpi_increase'] = json.loads(r.get("kpi_increase"))
    #data['kpi_boxoffice_total'] = round(float(r.get("kpi_boxoffice_total"))/800000,2)
    #data['kpi_cash_total'] = round(float(r.get("kpi_cash_total"))/200000,2)
    #data['kpi_increase_total'] = round(int(r.get("kpi_increase_total"))/150000,2)
    data['kpi_boxoffice_total'] = round(float(r.get("kpi_boxoffice_total"))/10000,2)
    data['kpi_cash_total'] = round(float(r.get("kpi_cash_total"))/10000,2)
    data['kpi_increase_total'] = round(int(r.get("kpi_increase_total"))/10000,2)
    data['kpi_boxoffice_m'] = round(float(r.get("kpi_boxoffice_m"))/10000,2)
    data['kpi_cash_m'] = round(float(r.get("kpi_cash_m"))/10000,2)
    data['kpi_increase_m'] = round(int(r.get("kpi_increase_m"))/10000,2)

    return render_to_response('slides.html', {"data":data})  
 
